Prepare Property Example

This example uses the Prepare property to specify that a query should be executed directly rather than first creating a temporary stored procedure on the server.

Sub PrepareX()

    Dim wrkODBC As Workspace
    Dim conPubs As Connection
    Dim qdfTemp As QueryDef
    Dim rstTemp As Recordset

    ' Create ODBCDirect Workspace object and open Connection
    ' object.
    Set wrkODBC = CreateWorkspace("", _
        "admin", "", dbUseODBC)
    Set conPubs = wrkODBC.OpenConnection("Publishers", , , _
        "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")

    Set qdfTemp = conPubs.CreateQueryDef("")

    With qdfTemp
        ' Because you will only run this query once, specify
        ' the ODBC SQLExecDirect API function. If you do
        ' not set this property before you set the SQL
        ' property, the ODBC SQLPrepare API function will
        ' be called anyway which will nullify any
        ' performance gain.
        .Prepare = dbQUnprepare
        .SQL = "UPDATE roysched " & _
            "SET royalty = royalty * 2 " & _
            "WHERE title_id LIKE 'BU____' OR " & _
            "title_id LIKE 'PC____'"
        .Execute
    End With

    Debug.Print "Query results:"

    ' Open recordset containing modified records.
    Set rstTemp = conPubs.OpenRecordset( _
        "SELECT * FROM roysched " & _
        "WHERE title_id LIKE 'BU____' OR " & _
        "title_id LIKE 'PC____'")

    ' Enumerate recordset.
    With rstTemp
        Do While Not .EOF
            Debug.Print , !title_id, !lorange, _
                !hirange, !royalty
            .MoveNext
        Loop
        .Close
    End With

    conPubs.Close
    wrkODBC.Close

End Sub